package util;

import bean.Users;
import dao.UsersDao;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class DBUtil {

    public static Connection getConn(){
        String DBDirverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String DBUrl = "jdbc:sqlserver://localhost:1433;DatabaseName=shop";
        String DBUser = "sa";
        String DBPwd = "zjb66666";
        Connection connection = null;
        try {
            Class.forName(DBDirverName);
            connection = DriverManager.getConnection(DBUrl, DBUser, DBPwd);
        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeDBResource(ResultSet rs, Statement stm, Connection con) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stm != null) {
                stm.close();
                stm = null;
            }
            if (con != null) {
                con.close();
                con = null;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
    public  void printTableDataForTest(){
        Connection conn=getConn();
        String sql="select * from users";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            rs=ps.executeQuery();;
            while(rs.next()){
                int id=rs.getInt(1);
                String tel=rs.getString("tel");
                String username=rs.getString(3);
                String pwd=rs.getString(4);
                String email=rs.getString(5);
                String sex=rs.getString(6);

                LocalDateTime createTimeValue = rs.getTimestamp("create_time").toLocalDateTime();
                DateTimeFormatter formatter = DateTimeFormatter.ofPattern("YYYY-MM-DD HH:MM:SS");
                String createTime = createTimeValue.format(formatter);

                System.out.print(id+"\t");
                System.out.print(tel+"\t");
                System.out.print(username+"\t");
                System.out.print(pwd+"\t");
                System.out.print(createTime+"\t");
                System.out.print(sex+"\t\n");

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeDBResource(rs, ps, conn);
        }

    }
    public static void main(String args[]){

//        new DBUtil().printTableDataForTest();
        Users users=new Users();
        users.setName("zjb");
        users.setPwd("a1212a200973d24c465cfde2c3e6b5ed");
        UsersDao usersDao=new UsersDao();
        usersDao.selectForLogin(users);
    }
}
